﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace ATM
{
    public class BankDatabase
    {
        public List<Account> bankAccounts;
        private MySqlConnection conn;
        private DataTable data;
        private MySqlDataAdapter da;
        private MySqlCommandBuilder cb;

        public ATM atm;

        public BankDatabase()
        {
            DatabaseConnection();
            bankAccounts = new List<Account>();
            GetAccount();
        }

        public void DatabaseConnection()
        {
            if (conn != null)
                conn.Close();

            string connStr = "server=localhost;user id=root; password=root; database=atm; pooling=false";

            try
            {
                conn = new MySqlConnection(connStr);
                conn.Open();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("连接到服务器出错: " + ex.Message);
            }
        }

        public void CloseConnection()
        {
            conn.Close();
        }

        public void GetAccount()
        {
            if (conn == null) DatabaseConnection();
            data = new DataTable();

            da = new MySqlDataAdapter("SELECT * FROM account", conn);
            cb = new MySqlCommandBuilder(da);

            da.Fill(data);

            int i;
            DataRow dr;
            try
            {
                for (i = 0; i < data.Rows.Count; i++)
                {
                    dr = data.Rows[i];
                    addAccount(new Account(dr["accountid"].ToString(), Convert.ToInt32(dr["password"].ToString()), Convert.ToDecimal(dr["balance"].ToString()), dr["bank"].ToString()));
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
        }

        public void addAccount(Account account)
        {
            this.bankAccounts.Add(account);
        }

        public Boolean delAccount(String id)
        {
            for (int i = 0; i < bankAccounts.Count; i++)
            {
                if (bankAccounts[i].getID() == id)
                {
                    bankAccounts.RemoveAt(i);
                    return true;
                }
            }
            return false;
        }

        public int findPasswordByID(String id)
        {
            for (int i = 0; i < bankAccounts.Count; i++)
            {
                if (bankAccounts[i].getID() == id)
                {
                    return bankAccounts[i].getPassword();
                }
            }
            return -1;
        }

        public String findBankByID(String id)
        {
            for (int i = 0; i < bankAccounts.Count; i++)
            {
                if (bankAccounts[i].getID() == id)
                {
                    return bankAccounts[i].getBank();
                }
            }
            return "Error";
        }

        public bool isValidAccount(String id)
        {
            for (int i = 0; i < bankAccounts.Count; i++)
            {
                if (bankAccounts[i].getID() == id)
                {
                    return true;
                }
            }
            return false;
        }

        public Decimal balanceInquiry(String id)
        {
            for (int i = 0; i < bankAccounts.Count; i++)
            {
                if (bankAccounts[i].getID() == id)
                {
                    return bankAccounts[i].getAmount();
                }
            }

            return 0;
        }

        public String toString()
        {
            String result = "";

            for (int i = 0; i < bankAccounts.Count; i++)
            {
                result += bankAccounts[i].getID() + " " + bankAccounts[i].getPassword() + "\n";
            }

            return result;
        }

        public void UpdateDatabase()
        {
            if (conn == null) DatabaseConnection();

            data = new DataTable();

            da = new MySqlDataAdapter("SELECT * FROM account", conn);
            cb = new MySqlCommandBuilder(da);

            da.Fill(data);

            int i;
            try
            {
                for (i = 0; i < data.Rows.Count; i++)
                {
                    data.Rows[i]["accountid"] = bankAccounts[i].getID();
                    data.Rows[i]["password"] = bankAccounts[i].getPassword();
                    data.Rows[i]["bank"] = bankAccounts[i].getBank();
                    data.Rows[i]["balance"] = bankAccounts[i].getAmount();
                }
                for (i = data.Rows.Count; i < bankAccounts.Count; i++)
                {
                    InsertAccount(bankAccounts[i]);

                }
                da.Update(data);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }

        }

        public void InsertAccount(Account acc)
        {
            if (conn == null) DatabaseConnection();

            data = new DataTable();

            da = new MySqlDataAdapter("SELECT * FROM account", conn);
            cb = new MySqlCommandBuilder(da);

            da.Fill(data);

            DataRow dr = data.NewRow();
            try
            {
                dr["accountid"] = acc.getID();
                dr["password"] = acc.getPassword();
                dr["bank"] = acc.getBank();
                dr["balance"] = acc.getAmount();
                data.Rows.Add(dr);

                da.Update(data);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
        }

        public void DeleteAccount(String accid)
        {
            if (conn == null) DatabaseConnection();

            data = new DataTable();

            da = new MySqlDataAdapter("SELECT * FROM account", conn);
            cb = new MySqlCommandBuilder(da);

            da.Fill(data);

            int i;

            try
            {
                for (i = 0; i < data.Rows.Count; i++)
                {
                    if (data.Rows[i]["accountid"].ToString().Equals(accid))
                    {
                        data.Rows.RemoveAt(i);
                        break;
                    }
                }
                da.Update(data);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
        }
    }
}
